package com.example.hidesms.dao;

import android.database.Cursor;
import android.util.Log;

import com.example.hidesms.dto.SMSItem;
import com.example.hidesms.sqlite.DataBaseHelper;

public class SmsDAO implements ISmsDAO{
	public static String TABLE_NAME = "SMS";
	private static SmsDAO instance;

	public static final String CREATE_TABLE_SMS = "" + "CREATE TABLE SMS ("
			+ "SmsID     integer PRIMARY KEY AUTOINCREMENT,"
			+ "Address       nvarchar(20)," 
			+ "AddressName       nvarchar(20)," 
			+ "MsgBody       ntext," 
			+ "ReadState     boolean," 
			+ "\"Time\"   nvarchar(50),"
			+ "TimeFormated   nvarchar(50)," 
			+ "FolderName     nvarchar(30),"
			+ "Image     varbinary(100),"
			+ "PhotoUri    nvarchar(100)" +");"; 

	public static SmsDAO getInstance() {
		if (instance == null) {
			instance = new SmsDAO();
		}

		return instance;
	}

	@Override
	public boolean insert(DataBaseHelper myDatabase, SMSItem sms) {
		String sql = "INSERT INTO "
				+ TABLE_NAME
				+ "(Address, AddressName, MsgBody, ReadState, Time, TimeFormated, FolderName, PhotoUri, Image) "
				+ " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			myDatabase.ExeSQLData(
					sql,
					new Object[] { sms.getAddress(), sms.getAddressNameS(), sms.getMsg(), sms.getReadState(), sms.getTime(), sms.getTimeFormated(), sms.getFolderName(), sms.getPhotoUri(), sms.getImage() });
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}

		return true;
	}

	@Override
	public boolean update(DataBaseHelper myDatabase, SMSItem sms) {
		String sql = "UPDATE " + TABLE_NAME + " SET " + "Address = ?," + "AddressName = ?,"
				+ "MsgBody = ?," + "ReadState = ?," + "Time = ?," + "TimeFormated = ?,"+"FolderName = ?,"
				+ "PhotoUri = ?" + "Image = ?"
				+ " WHERE SmsID = ?";
		try {
			myDatabase.ExeSQLData(
					sql,
					new Object[] { sms.getAddress(), sms.getAddressNameS(), sms.getMsg(), sms.getReadState(), sms.getTime(), sms.getTimeFormated(), sms.getFolderName(), sms.getPhotoUri()  });
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}

		return true;
	}

	@Override
	public boolean delete(DataBaseHelper myDatabase, int smsId) {
		String sql = "DELETE FROM " + TABLE_NAME + " WHERE SmsID = " + smsId;
		try {
			myDatabase.ExeSQLData(sql);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}

		return true;
	}

	@Override
	public Cursor getAll(DataBaseHelper myDatabase, String folderGroup, boolean flag) {
		String sql = "SELECT * FROM " + TABLE_NAME+" n ";// + " n WHERE n.FolderName =" + folderGroup;
		if (folderGroup !=null) {
			sql += " WHERE n.FolderName ="+"'"+folderGroup+"'";
		}
		if (flag) {
			sql += " GROUP BY n.AddressName"; 
		}
		sql+=" ORDER BY AddressName ASC";
		Cursor cursor = null;
		try {
			cursor = myDatabase.QueryData(sql);
		} catch (Exception e) {
			e.printStackTrace();

		}
		Log.d("test", "" + cursor == null ? "null" : "not null");
		return cursor;
	}

	@Override
	public Cursor getById(DataBaseHelper myDatabase, int smsId) {
		String sql = "SELECT * FROM " + TABLE_NAME + " n WHERE n.SmsID =" + smsId;
		Cursor cursor = null;
		try {
			cursor = myDatabase.QueryData(sql);
		} catch (Exception e) {
			e.printStackTrace();

		}
		Log.d("test", "" + cursor == null ? "null" : "not null");
		return cursor;
	}

	@Override
	public Cursor getAllByRead(DataBaseHelper myDatabase, String read) {
		String sql = "SELECT * FROM " + TABLE_NAME + " n WHERE n.ReadState =" +"'"+read+"'";
//		if ("1".equals(read)) {
//			sql += " n WHERE n.ReadState ="+read;
//		}
		Cursor cursor = null;
		try {
			cursor = myDatabase.QueryData(sql);
		} catch (Exception e) {
			e.printStackTrace();

		}
		Log.d("test", "" + cursor == null ? "null" : "not null");
		return cursor;
	}

	@Override
	public Cursor getByAdress(DataBaseHelper myDatabase, String address) {
		String sql = "SELECT * FROM " + TABLE_NAME + " n WHERE n.AddressName LIKE" +"'"+address+"'"+" ORDER BY TIME DESC";
		Cursor cursor = null;
		try {
			cursor = myDatabase.QueryData(sql);
		} catch (Exception e) {
			e.printStackTrace();

		}
		Log.d("test", "" + cursor == null ? "null" : "not null");
		return cursor;
	}

}
